Splitting, Merging, and Replacing Column Values in Excel

Course: Advanced Excel

Topic: Splitting, Merging, and Replacing Column Values

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction

Data cleaning is an essential part of working with Microsoft Excel. Raw data often contains combined values, inconsistent text, unnecessary spaces, spelling variations, and information stored in the wrong columns. These problems can affect sorting, filtering, formulas, PivotTables, charts, and reports.

Excel provides several features for organizing and standardizing information. Splitting separates one column into multiple columns, merging combines information from multiple columns, and replacing changes specific values into standardized or corrected values.

These operations are useful when preparing customer lists, employee records, sales transactions, product catalogs, addresses, inventory reports, and datasets imported from external systems.

Learning Objectives

• Understand splitting, merging, and replacing column values.
• Split text using delimiters such as spaces, commas, hyphens, and pipes.
• Use Text to Columns and Flash Fill to reorganize information.
• Combine text values using concatenation, CONCAT, and TEXTJOIN.
• Replace selected values using Find and Replace and SUBSTITUTE.
• Use Power Query to split, merge, and replace values during data preparation.
• Handle blank cells, extra spaces, missing values, and inconsistent labels.
• Apply these techniques to practical business datasets.

2. Understanding Splitting, Merging, and Replacing

These operations are related but solve different problems. For example, a dataset containing the full name Rahul Sharma may need splitting to create First Name and Last Name columns. If the names are stored separately, merging can create a Full Name column. If a city is recorded as Mum in some rows and Mumbai in others, replacing can standardize the city name.

3. Preparing Data Before Editing

Before splitting, merging, or replacing values, inspect the source data. A small preparation step helps prevent accidental changes and makes the results more reliable.

Recommended Preparation Steps

1. Save a backup copy of the original workbook.
2. Identify the columns that need to be cleaned.
3. Check whether the data contains headers, blank rows, or merged cells.
4. Inspect a sample of records to understand the pattern.
5. Look for leading spaces, trailing spaces, inconsistent separators, and missing values.
6. Convert the dataset into an Excel Table when appropriate by selecting the range and pressing Ctrl+T.
7. Decide whether the transformation should be a one-time change or a repeatable process.

For recurring data preparation, Power Query is often preferable because it records transformation steps and can apply them again when the source data is refreshed.

4. Splitting Column Values with Text to Columns

Text to Columns is an Excel feature that divides text from one column into multiple columns. It is useful when values follow a predictable pattern, such as a comma-separated list, a full name separated by spaces, or a code divided by hyphens.

Example: Splitting Full Names

Suppose a worksheet contains full names in column A.

The objective is to separate the names into First Name and Last Name columns.

Step-by-Step Procedure

1. Select the cells containing the full names.
2. Open the Data tab.
3. Select Text to Columns.
4. Choose Delimited if a character separates the values.
5. Select Space as the delimiter.
6. Review the preview to ensure the names are divided correctly.
7. Choose a destination if you want to preserve the original column.
8. Click Finish.

The result should place the first and last names in separate columns for the sample data.

Delimited vs. Fixed Width

Use Delimited when a separator such as a comma, space, or hyphen defines the boundary. Use Fixed Width when values are consistently positioned at the same character locations.

5. Splitting Data with Different Delimiters

A delimiter is a character or symbol that separates one value from another. Common delimiters include spaces, commas, semicolons, hyphens, underscores, slashes, and vertical bars.

To split values using a custom delimiter, open Text to Columns, select Delimited, and choose Other if the required character is not listed. Enter the delimiter and inspect the preview before completing the operation.

Important Considerations

• Repeated delimiters may create empty columns.
• Names can contain middle names or multiple surnames, so splitting on every space may not produce the intended structure.
• Addresses may contain commas within individual fields, so confirm the source format before splitting.
• Splitting dates into separate numbers can change how Excel interprets the data.
• Always select an appropriate destination if you need to retain the original values.

6. Splitting Column Values with Excel Formulas

Formulas are useful when you want to split text dynamically without manually repeating Text to Columns. The formula results can update when the source value changes.

Example Dataset

Assume the original full name is in cell A2.

Extract the First Name

=TEXTBEFORE(A2," ")

TEXTBEFORE returns the text before the first space. This function is available in supported modern Excel versions.

Extract the Last Name

=TEXTAFTER(A2," ",-1)

TEXTAFTER with -1 returns the text after the last space, which is helpful when a name contains a middle name. These examples assume that the cell contains a properly formatted name with at least one space.

Alternative Formulas for Older Excel Versions

To extract the first word before the first space:

=LEFT(A2,FIND(" ",A2)-1)

To extract the text after the first space:

=MID(A2,FIND(" ",A2)+1,LEN(A2))

The second formula returns everything after the first space, so it can retain a middle name if one exists. If cells can be blank or contain only one word, add error handling or use a formula designed for those cases.

Using TEXTSPLIT

In supported Excel versions, TEXTSPLIT can separate text using one or more delimiters.

=TEXTSPLIT(A2," ")

This formula spills the separated words into neighboring cells. Ensure that the destination cells are empty so the results can expand.

7. Using Flash Fill to Split Values

Flash Fill recognizes a pattern from examples that you enter and fills the remaining cells based on that pattern. It is useful for extracting first names, last names, codes, or portions of text without writing formulas.

Example

Suppose column A contains full names. In B2, type Rahul when A2 contains Rahul Sharma. In B3, type Priya when A3 contains Priya Mehta. Then select the remaining cells in column B and use Data > Flash Fill, or press Ctrl+E on supported Windows versions.

Steps

1. Keep the original data in one column.
2. Enter the desired output for the first few rows.
3. Ensure that the examples consistently demonstrate the intended pattern.
4. Use Flash Fill from the Data tab or press Ctrl+E.
5. Review the generated values carefully.

Flash Fill creates values rather than a formula-driven transformation. If the source data changes later, the filled values generally do not update automatically.

8. Merging Column Values with Concatenation

Merging text values is useful when information is stored in separate columns but needs to be displayed together. Common examples include combining first and last names, creating full addresses, building product descriptions, and constructing reference labels.

Example Dataset

Method 1: Using the Ampersand Operator

=A2&" "&B2

The ampersand (&) combines the value in A2, a space, and the value in B2. This method is simple and works in many Excel versions.

Method 2: Using CONCAT

=CONCAT(A2," ",B2)

CONCAT joins text from multiple cells or text values. It does not automatically insert spaces or punctuation, so add separators explicitly where required.

Method 3: Using TEXTJOIN

=TEXTJOIN(" ",TRUE,A2,B2)

TEXTJOIN combines values using a specified delimiter. Its second argument, TRUE, tells Excel to ignore empty values. This can be helpful when some records have missing middle names or optional address components.

Method 4: Using CONCATENATE in Older Workbooks

=CONCATENATE(A2," ",B2)

CONCATENATE is supported in many older Excel workbooks. For new work, CONCAT or TEXTJOIN is generally more flexible.

9. Merging Multiple Columns into One Value

Sometimes a record contains multiple fields that need to be combined into a single label or descriptive value.

Example: Full Address

To combine the address fields with separators, use:

=TEXTJOIN(", ",TRUE,A2:D2)

The result is a comma-separated address. If a field is blank, TEXTJOIN with TRUE can skip that empty field.

Example: Creating a Product Label

Suppose A2 contains a product name, B2 contains a color, and C2 contains a size. Use:

=TEXTJOIN(" - ",TRUE,A2:C2)

The output might be Laptop - Silver - Medium, depending on the values in the source cells.

Using TEXT with Numbers and Dates

When merging dates or numbers into text, use the TEXT function if you need a specific display format.

=A2&" - "&TEXT(B2,"dd-mmm-yyyy")

If A2 contains an order ID and B2 contains an Excel date, this formula joins the ID and a formatted date. Without TEXT, Excel may display a date as its underlying serial number in a concatenated string.

10. Merging Columns in Power Query

Power Query provides a Merge Columns feature for combining selected columns into one column during data preparation. This is different from Merge Queries, which joins separate tables using matching fields.

Step-by-Step Procedure

1. Import the dataset or select a query and open Power Query Editor.
2. Select the columns you want to combine. Use Ctrl-click to select multiple columns where supported.
3. Open the Transform tab.
4. Choose Merge Columns.
5. Select a separator such as a space, comma, hyphen, or custom delimiter.
6. Enter a meaningful name for the new column.
7. Confirm the operation and inspect the results.
8. Select Close & Load when the transformation is complete.

Power Query records the merge as a transformation step, allowing the same operation to run again when the query is refreshed.

Merge Columns vs. Merge Queries

11. Replacing Values with Find and Replace

Find and Replace is a quick way to correct repeated text, standardize labels, or replace a value throughout a selected range or worksheet.

Example: Standardizing City Names

Suppose a city column contains Mumbai, Mum, and MUM. If all three labels are intended to represent the same city, the inconsistent versions can be standardized to Mumbai.

Step-by-Step Procedure

1. Select the cells or column where the replacement should occur.
2. Press Ctrl+H to open Find and Replace in supported Excel versions.
3. Enter the existing value in the Find what field.
4. Enter the corrected value in the Replace with field.
5. Choose Replace to review changes one at a time, or Replace All to apply them throughout the selected search scope.
6. Review the results and verify that unrelated values were not changed.

Important Settings

• Within: Specifies whether the search applies to the sheet or workbook, depending on available options.
• Search: Can specify whether Excel searches formulas or values.
• Match case: Distinguishes uppercase and lowercase text when enabled.
• Match entire cell contents: Prevents a short search term from matching as part of a longer cell value.

For example, replacing Mum with Mumbai without checking the search scope and matching options can affect values that contain the same characters as part of a larger phrase. Review the intended scope before using Replace All.

12. Replacing Text with the SUBSTITUTE Function

SUBSTITUTE replaces specific text within a cell and returns a new result. Unlike Find and Replace, it can be used in formulas to create a dynamic cleaned value while preserving the original source column.

Basic Syntax

=SUBSTITUTE(text,old_text,new_text,[instance_num])

• text: The original text or cell reference.
• old_text: The text that should be replaced.
• new_text: The replacement text.
• instance_num: Optional; specifies which occurrence to replace.

Example 1: Replace an Abbreviation

If A2 contains Mum, the formula below returns Mumbai.

=SUBSTITUTE(A2,"Mum","Mumbai")

Example 2: Replace a Hyphen with a Space

If A2 contains North-West, use:

=SUBSTITUTE(A2,"-"," ")

The result is North West.

Example 3: Replace Only the First Occurrence

If A2 contains A-B-C, the formula below replaces only the first hyphen.

=SUBSTITUTE(A2,"-"," ",1)

The result is A B-C.

Example 4: Remove a Specific Character

To remove a hyphen from an identifier, use:

=SUBSTITUTE(A2,"-","")

Use this only when removing the character is appropriate. Do not remove separators from values such as dates or identifiers if doing so would make them ambiguous.

13. Replacing Values with the REPLACE Function

REPLACE replaces a specified number of characters starting at a given position. It differs from SUBSTITUTE, which searches for matching text.

Syntax

=REPLACE(old_text,start_num,num_chars,new_text)

Example

Suppose A2 contains ABC12345 and you want to replace the first three characters with XYZ.

=REPLACE(A2,1,3,"XYZ")

The result is XYZ12345.

14. Replacing Values in Power Query

Power Query provides a Replace Values option that applies a replacement step to a query. This is especially useful when the same cleanup needs to be repeated whenever the source data changes.

Step-by-Step Procedure

1. Open Power Query Editor from the Data tab.
2. Select the column containing the values that need to be standardized.
3. Right-click the column or use the Transform tab.
4. Choose Replace Values.
5. Enter the value to find and the replacement value.
6. Confirm the operation.
7. Review the preview to ensure the intended values were changed.
8. Repeat for other known inconsistencies if necessary.
9. Choose Close & Load to return the results to Excel.

Example: Standardizing Department Names

Apply separate replacements where needed, then review the distinct values in the column to confirm that the categories are consistent. Replacement behavior depends on the data type and selected operation, so check the preview before loading the results.

15. Removing Extra Spaces and Cleaning Text

Extra spaces and hidden characters can cause matching, sorting, and lookup problems. For example, Excel may treat "Mumbai" and "Mumbai " as different text values in certain operations.

Using TRIM

=TRIM(A2)

TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to a single space. It does not remove every type of nonbreaking space or hidden character.

Using CLEAN

=CLEAN(A2)

CLEAN removes many nonprinting characters from text. It may not remove all unusual Unicode characters found in externally imported data.

Combining TRIM and CLEAN

=TRIM(CLEAN(A2))

This formula is a useful first step for cleaning text imported from external sources. If the source contains nonbreaking spaces, an additional SUBSTITUTE step may be needed. For example, in common cases where CHAR(160) represents the unwanted space:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Always check the output, especially when the original data includes intentional spaces or special characters.

16. Handling Blank Values and Missing Information

Missing values can affect splitting, merging, and replacing operations. A formula that combines two cells may leave extra separators when one cell is blank. A replacement operation may also fail to standardize values if blanks are not handled deliberately.

Example: Merging Names with Optional Middle Names

Suppose A2 contains First Name, B2 contains Middle Name, and C2 contains Last Name. Use:

=TEXTJOIN(" ",TRUE,A2:C2)

The formula joins the available names with spaces and ignores blank cells.

Example: Replacing a Blank with a Label

If A2 may contain an empty value and you want to display Not Provided in a separate output column, use:

=IF(A2="","Not Provided",A2)

This formula is useful for reporting, but do not replace missing values automatically if a blank has a specific business meaning. For example, a blank completion date may mean that a task is still open.

17. Splitting, Merging, and Replacing in Power Query: Practical Workflow

For repeatable data-cleaning tasks, it is often best to perform the operations in Power Query rather than manually changing cells every time a file is received.

Recommended Workflow

1. Import the source data using Data > Get Data.
2. Inspect the column names and data types.
3. Use Split Column to separate combined values.
4. Use Merge Columns to combine values when necessary.
5. Use Replace Values to standardize known inconsistencies.
6. Trim and clean text columns where appropriate.
7. Handle nulls, errors, and duplicate records according to business rules.
8. Review the Applied Steps list to confirm the transformation sequence.
9. Load the cleaned result into a worksheet or Data Model.
10. Refresh the query when the source changes and validate the output.

Because Power Query records the transformation steps, this workflow can reduce repetitive manual effort. However, changes to source structure or unexpected new values may require the query to be updated.

18. Common Mistakes and Troubleshooting

19. Best Practices

• Keep a copy of the original source data before performing bulk changes.
• Use Text to Columns for straightforward one-time splits and Power Query for repeatable transformations.
• Use formulas when you want output values to respond to changes in the source cells.
• Use Flash Fill for quick pattern-based transformations, but validate its output.
• Use TEXTJOIN when combining multiple fields that may contain blanks.
• Use SUBSTITUTE for matching text patterns and REPLACE for position-based changes.
• Be careful when using Replace All across an entire worksheet or workbook.
• Review data types before and after a transformation.
• Check for hidden spaces and nonprinting characters when text values do not match.
• Document important transformation rules so the process can be repeated accurately.
• Verify the results by comparing row counts, sample records, and relevant totals.

20. Practical Exercise

Scenario: You receive an employee dataset containing full names, department abbreviations, and location values. Some entries have extra spaces, and some departments use inconsistent labels.

Sample Data

Exercise Tasks

1. Create a backup of the source data.
2. Remove unwanted spaces from the Full Name and City values.
3. Split Full Name into First Name and Last Name.
4. Standardize HR and H.R. to Human Resources.
5. Standardize Mum to Mumbai.
6. Create a Full Name column from the split name fields to practise merging.
7. Compare the original and cleaned data to confirm the changes.
8. Repeat the same workflow in Power Query to practise a refreshable transformation.

Expected Result

21. Mini Project: Clean and Standardize a Sales Dataset

Project Objective: Prepare a sales dataset containing combined product codes, customer names, inconsistent region labels, and incomplete values.

Project Requirements

• Split product codes such as PROD-2026-015 into separate code components.
• Separate full customer names into appropriate fields where the naming pattern allows it.
• Merge first and last names into a consistent Full Name column.
• Replace known region abbreviations with standardized region names.
• Remove unnecessary spaces and selected nonprinting characters.
• Handle blank fields without introducing unwanted separators.
• Document which replacements were applied and why.
• Load the cleaned dataset into Excel and build a PivotTable summarizing sales by region.
• Test the workflow on a second copy of the source data.

Project Deliverables

• Original source dataset.
• Cleaned and standardized dataset.
• Documented formulas or Power Query transformation steps.
• A summary report with verified totals.
• A brief explanation of the data-quality issues discovered and corrected.

22. Interview Questions and Answers

Q1. What is the purpose of splitting column values in Excel?

Answer: Splitting separates combined information into individual fields, such as extracting first and last names or separating components of a product code.

Q2. What is Text to Columns?

Answer: Text to Columns is an Excel feature that divides text from one column into multiple columns using delimiters or fixed character positions.

Q3. What is the difference between Delimited and Fixed Width?

Answer: Delimited splits text at specified separator characters, while Fixed Width splits text at selected character positions.

Q4. How can you combine two columns into one?

Answer: You can use the ampersand operator, CONCAT, TEXTJOIN, or Power Query's Merge Columns feature, depending on whether you need a formula-based or repeatable transformation.

Q5. What is the difference between CONCAT and TEXTJOIN?

Answer: CONCAT joins values without automatically adding a separator. TEXTJOIN lets you specify a delimiter and optionally ignore empty values.

Q6. What is the difference between SUBSTITUTE and REPLACE?

Answer: SUBSTITUTE replaces matching text, while REPLACE changes a specified number of characters starting at a specified position.

Q7. How do you open Find and Replace in Excel?

Answer: Press Ctrl+H in supported Excel versions, enter the text to find and the replacement value, and review the search scope before making changes.

Q8. What is Flash Fill?

Answer: Flash Fill recognizes a pattern from examples and fills the remaining cells with corresponding values. Its results are generally static rather than formula-driven.

Q9. Why might text values fail to match even when they look identical?

Answer: They may contain leading or trailing spaces, nonbreaking spaces, hidden characters, or inconsistent spelling and capitalization.

Q10. How can Power Query help with data cleaning?

Answer: Power Query records transformation steps for splitting, merging, replacing, filtering, and cleaning values. The steps can be reapplied when the source data is refreshed.

Q11. How do you combine values when some cells are blank?

Answer: Use TEXTJOIN with the ignore_empty argument set to TRUE, provided that skipping blank values is appropriate for the business requirement.

Q12. Why should you preserve the original data?

Answer: Keeping the original data allows you to verify changes, recover from mistakes, and compare the source with the cleaned result.

23. Quick Revision Checklist

• Understand the difference between splitting, merging, and replacing values.
• Use Text to Columns to split text using delimiters or fixed positions.
• Use TEXTBEFORE, TEXTAFTER, TEXTSPLIT, or alternative formulas where supported.
• Use Flash Fill for pattern-based transformations and verify the results.
• Use CONCAT, TEXTJOIN, or the ampersand operator to merge values.
• Use Find and Replace for direct edits, with care around search scope.
• Use SUBSTITUTE for matching text and REPLACE for position-based changes.
• Use TRIM and CLEAN to handle common spacing and character issues.
• Use Power Query for repeatable data-cleaning workflows.
• Check blank values, data types, and output accuracy before reporting.

24. Conclusion

Splitting, merging, and replacing column values are fundamental data-cleaning techniques in Advanced Excel. They help transform raw data into a consistent structure that is easier to analyze, summarize, and report.

Text to Columns and Flash Fill are useful for quick transformations, formulas provide dynamic results, and Power Query is valuable for repeatable data preparation. Choosing the right technique and validating the output can improve data quality and reduce manual errors in everyday business reporting.

To develop more spreadsheet and data-analysis skills, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy website, or register for a course demo.

25. Additional Learning Resources

• Microsoft Support: Excel Help and Learning
• Microsoft Support: Split Text into Different Columns
• Microsoft Excel: Formulas, Text Functions, and Data Tools